﻿component
{
	include "../../inc/lib.cfm";
	//列表操作
	public any function list(any rc)
	{
		var result = {};
		//从对象中进行Hql模式查询
		var offset = (rc.page-1)*rc.rows;
		hql = "from supplier where " & rc.sqlWhere & rc.sqlSort;
		var result["rows"] = ormExecuteQuery(hql, false, {maxResults=rc.rows, offset=offset});
		var totalhql = "select count(id) as total from supplier where " & rc.sqlWhere ;
		var result["total"] = ormExecuteQuery(totalhql, true);

		return result;
	}

	//删除操作
	public any function delete(numeric id){
		transaction {
            var supplier = entityLoadByPk("supplier", arguments.id);
           	entityDelete(supplier);
            transactionCommit();
        }
	}

	//获取操作
	public any function get(numeric id){
		result = entityLoadByPk("supplier", arguments.id);
		return result;
	}

	//新增操作
	public any function save(any rc){
		transaction {
            var q = entityNew("supplier");
			include "../../inc/saveandupdatenotcommit.cfm";

			//如果英文名称没有数据，自动转换中文名称到英文名称
			if( len(trim(rc.supenname)) == 0){
				var supenname = translateToEn(rc.supchsname);
				q.setsupenname(supenname);
			}

			//中文全拼
			if( len(trim(rc.suppyfullspell)) == 0  ){
				var suppyfullspell 		= getPySpell(rc.supchsname);
				q.setsuppyfullspell(suppyfullspell);
			}
			//中文拼音首字母
			if( len(trim(rc.suppysimplespell)) == 0 ){
				var suppysimplespell 	= getPySpell(rc.supchsname,1);
				q.setsuppysimplespell(suppysimplespell);
			}

			//证书图片
			var u = entityLoadByPk("uploader", rc.gmporgsppicid);
			q.setgmporgsppic('uploads/' & u.getservername());

			entitySave(q);
			transactionCommit();
        }
        return q;
	}

	//修改操作
	public any function update(any rc){
		transaction {
            var q = entityLoadByPk("supplier", rc.id);
			include "../../inc/saveandupdatenotcommit.cfm";
			//如果英文名称没有数据，自动转换中文名称到英文名称
			if( len(trim(rc.supenname)) == 0){
				var supenname = translateToEn(rc.supchsname);
				q.setsupenname(supenname);
			}

			//中文全拼
			if( len(trim(rc.suppyfullspell)) == 0  ){
				var suppyfullspell 		= getPySpell(rc.supchsname);
				q.setsuppyfullspell(suppyfullspell);
			}

			//中文拼音首字母
			if( len(trim(rc.suppysimplespell)) == 0 ){
				var suppysimplespell 	= getPySpell(rc.supchsname,1);
				q.setsuppysimplespell(suppysimplespell);
			}

			//证书图片
			if( len(trim(rc.gmporgsppicid)) > 0 ){
				var u = entityLoadByPk("uploader", rc.gmporgsppicid);
				q.setgmporgsppic('uploads/' & u.getservername());
			}

        }
        return q;
	}

	//发布操作
	public any function publish(numeric id){
		q = entityLoadByPk("supplier", arguments.id);
		if( q.getispublish()){v = 0;}else{v = 1;}
		q.setispublish(v);
		entitySave(q);
		transactionCommit();
		return v;
	}


	public any function importFile(any rc){
		transaction {
            var q = entityLoadByPk("uploader", rc.excelid);
			var f = expandpath('uploads/' & q.getservername());

			var qz = entityLoadByPk("uploader", rc.imgzipid);
			var fz = expandpath('uploads/' & qz.getservername());
			var unzipPath = 'uploads/certificate/' & rc.imgzipid & '/';
			var unzipDes = expandpath(unzipPath);

			if(!DirectoryExists(unzipDes)){
				directory action = "create" directory ="#unzipDes#";
			}

			zip action="unzip" destination="#unzipDes#" file="#fz#";
			directory action="list" directory="#unzipDes#" listinfo="name" name="zipFile";

			objPOI = new cfc.POIUtility();
			arrSheets = objPOI.ReadExcel( FilePath = f,HasHeaderRow = true);

			var fields = ['供应商名称','英文名称','供应商编码','营业执照注册号','许可证类型','法定代表人','企业负责人','质量负责人','仓库地址','地址','电话','生产经营范围','认证范围','有效期开始时间','有效期结束时间','联系代理人','生产(经营)许可证号','采购员','GMP(GSP)认证','引进原因','备注'];

			var column = arrSheets[1].COLUMNNAMES;
			var pos = [];
			for(var i=1; i<= arraylen(column); i++){
				pos.append(arrayfindnocase(fields,column[i]));
			}

			var qry = arrSheets[1].Query;

			for(var i=1; i<= qry.RecordCount; i++){
				var supchsname 			= evaluate("qry.column"  & arrayfind(pos,1) & '[' & i& ']');
				var supenname 			= evaluate("qry.column"  & arrayfind(pos,2) & '[' & i& ']');
				var supcode		 		= evaluate("qry.column"  & arrayfind(pos,3) & '[' & i& ']');
				var buslicense			= evaluate("qry.column"  & arrayfind(pos,4) & '[' & i& ']');
				var licencetype			= evaluate("qry.column"  & arrayfind(pos,5) & '[' & i& ']');
				var corporation		 	= evaluate("qry.column"  & arrayfind(pos,6) & '[' & i& ']');
				var companyprincipal	= evaluate("qry.column"  & arrayfind(pos,7) & '[' & i& ']');
				var qualityprincipal 	= evaluate("qry.column"  & arrayfind(pos,8) & '[' & i& ']');
				var storageaddress		= evaluate("qry.column"  & arrayfind(pos,9) & '[' & i& ']');
				var suppleaddress		= evaluate("qry.column"  & arrayfind(pos,10) & '[' & i& ']');
				var phone			 	= evaluate("qry.column"  & arrayfind(pos,11) & '[' & i& ']');
				var scope				= evaluate("qry.column"  & arrayfind(pos,12) & '[' & i& ']');
				var approvescope	 	= evaluate("qry.column"  & arrayfind(pos,13) & '[' & i& ']');
				var startdate			= evaluate("qry.column"  & arrayfind(pos,14) & '[' & i& ']');
				var enddate				= evaluate("qry.column"  & arrayfind(pos,15) & '[' & i& ']');
				var contactagent	 	= evaluate("qry.column"  & arrayfind(pos,16) & '[' & i& ']');
				var lesence				= evaluate("qry.column"  & arrayfind(pos,17) & '[' & i& ']');
				var bailer			 	= evaluate("qry.column"  & arrayfind(pos,18) & '[' & i& ']');
				var gmporgspno			= evaluate("qry.column"  & arrayfind(pos,19) & '[' & i& ']');
				var importcause			= evaluate("qry.column"  & arrayfind(pos,20) & '[' & i& ']');
				var content				= evaluate("qry.column"  & arrayfind(pos,21) & '[' & i& ']');

				var hql = "from supplier where supcode = '#supcode#' and supchsname = '#supchsname#'";
				var result = ormExecuteQuery(hql,true);


				if(isnull(result)){
					var q = entityNew("supplier");
				}else{
					var q = entityLoadByPk("supplier", result.getid());
				}

				//如果英文名称没有数据，自动转换中文名称到英文名称
				if( len(trim(supenname)) == 0){
					supenname = translateToEn(supchsname);
				}
				//中文全拼
				suppyfullspell 		= getPySpell(supchsname);
				//中文拼音首字母
				suppysimplespell 	= getPySpell(supchsname,1);

				q.setsuppyfullspell(suppyfullspell);
				q.setsuppysimplespell(suppysimplespell);
				q.setsupchsname(supchsname);
				q.setsupenname(supenname);
				//通过编码对应解压缩文件中的编码图是否存在，去存储gmpOrgsp证书的图片路径
				//查询中的查询Query of Query
				query name="fileList" dbtype="query"{
					echo("SELECT top 1 name FROM zipFile where name like '#supcode#.%'");
				}

				if(fileList.recordcount > 0){
					q.setgmporgsppic(unzipPath & fileList.name);
				}

				q.setsupcode(supcode);
				q.setbuslicense(buslicense);
				q.setlicencetype(licencetype);
				q.setcorporation(corporation);
				q.setcompanyprincipal(companyprincipal);
				q.setqualityprincipal(qualityprincipal);
				q.setstorageaddress(storageaddress);
				q.setsuppleaddress(suppleaddress);
				q.setphone(phone);
				q.setscope(scope);
				q.setapprovescope(approvescope);
				q.setstartdate(startdate);
				q.setenddate(enddate);
				q.setcontactagent(contactagent);
				q.setlesence(lesence);
				q.setbailer(bailer);
				q.setgmporgspno(gmporgspno);
				q.setimportcause(importcause);
				q.setremark(content);

				entitySave(q);

			}

			transactionCommit();
        }
        return q;
	}


	public any function exportFile(){
		objPOI = new cfc.POIUtility();
	  	qry = new Query(); // new query object
	    qry.setSQL("select * from supplier"); //set query
	    qryRes = qry.execute(); // execute query
		objSheet = objPOI.GetNewSheetStruct();
		objSheet.Query = qryRes.getResult();
		objSheet.ColumnList = "supchsname,supenname,supcode,buslicense,licencetype,corporation,companyprincipal,qualityprincipal,storageaddress,suppleaddress,phone,scope,approvescope,startdate,enddate,contactagent,lesence,bailer,gmporgspno,importcause,content";
		objSheet.ColumnNames = "供应商名称,英文名称,供应商编码,营业执照注册号,许可证类型,法定代表人,企业负责人,质量负责人,仓库地址,地址,电话,生产经营范围,认证范围,有效期开始时间,有效期结束时间,联系代理人,生产(经营)许可证号,采购员,GMP(GSP)认证,引进原因,备注";
		objSheet.SheetName = "Supplier";

		objPOI.WriteExcel(FilePath = ExpandPath( "./supplier.xls" ),Sheets = objSheet);

		header  name="content-disposition" value="attachment; filename=supplier.xls";
		content file="#expandpath('./supplier.xls')#" deletefile="yes";
	}

}